package com.lanyou.esb.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.lanyou.esb.entity.Http;

/**
 * 服务重用度统计查询
 * 
 * @author Davey.wu
 */
public interface ServiceCheckRepository extends JpaRepository<Http, String> {
	/**
	 * 统计服务重用度信息
	 * 
	 * @param userId
	 *            ：用记标识
	 * @param interfaceName
	 *            ：接口名称
	 * @param interfaceType
	 *            ：接口类型
	 * @param startDate
	 *            ：开始时间
	 * @param endDate
	 *            ：结束时间
	 * @param reuseDegree
	 *            ：重用度
	 * @param auditStatus
	 *            ：审核状态
	 * @param pageNumber
	 *            ：页码
	 * @param pageSize
	 *            ：页大小
	 * @return
	 */
	@Query(nativeQuery = true, value = "SELECT V0.IF_HTTP_ID AS interfaceId, "
			+ " V0.IF_NAME AS interfaceName, "
			+ " V0.CREATED_DATE AS createdDate, "
			+ " V0.IF_TYPE AS interfaceType, "
			+ " V0.IF_VERSION AS version, "
			+ " V0.REMARK AS remark, "
			+ " (SELECT s.SYSTEM_NAME FROM t_cook_bu_field_system s WHERE s.FIELD_SYSTEM_ID=V0.FIELD_SYSTEM_ID) AS systemName, "
			+ " (SELECT COUNT(T.IF_TOKEN_ID) AS reuseAmount FROM t_esb_bu_if_token t WHERE t.IF_ID=V0.IF_HTTP_ID) AS reuseAmount, "
			+ " (SELECT f.FIELD_NAME FROM t_cook_bu_field_system s,t_cook_db_field f WHERE s.FIELD_SYSTEM_ID=V0.FIELD_SYSTEM_ID AND s.FIELD_ID=f.FIELD_ID) as fieldName, "
			+ " (SELECT GROUP_CONCAT(s.SYSTEM_NAME) FROM t_cook_bu_field_system s, t_esb_bu_if_token t0  WHERE s.FIELD_SYSTEM_ID=t0.IF_CALL_SYSTEM_ID and t0.IF_ID=V0.IF_HTTP_ID) AS reuseSystemName "
			+ " FROM (SELECT * FROM "
			+ " (SELECT h0.IF_HTTP_ID,h0.IF_NAME,h0.IF_VERSION,h0.FIELD_SYSTEM_ID,h0.CREATED_DATE,h0.IF_TYPE,h0.REMARK,"
			+ " (SELECT COUNT(s11.FIELD_SYSTEM_ID) FROM t_cook_bu_field_system s11, t_esb_bu_if_token t01  WHERE s11.FIELD_SYSTEM_ID=t01.IF_CALL_SYSTEM_ID and t01.IF_ID=h0.IF_HTTP_ID) AS totalNum "
			+ " FROM t_esb_bu_if_http h0 "
			+ " WHERE 1=1 "
			+ " AND (''=:auditStatus OR h0.AUDIT_STATUS =:auditStatus) "
			+ " AND (''=:interfaceName OR h0.IF_NAME LIKE :interfaceName) "
			+ " AND (''=:interfaceType OR h0.IF_TYPE =:interfaceType) "
			+ " AND (''=:startDate OR h0.CREATED_DATE >= :startDate) "
			+ " AND (''=:endDate OR h0.CREATED_DATE <= :endDate)  "
			+ " AND (''=:userId OR EXISTS(SELECT * FROM t_cook_bu_field_system S0,t_cook_bu_user_system U0 WHERE S0.FIELD_SYSTEM_ID=h0.FIELD_SYSTEM_ID AND S0.FIELD_SYSTEM_ID=U0.FIELD_SYSTEM_ID AND U0.USER_ID=:userId)) "
			+ " ) AS V1"
			+ " WHERE ''=:reuseDegree OR ('0'=:reuseDegree AND totalNum=0) OR ('1' =:reuseDegree AND totalNum  = 1) OR ('2'=:reuseDegree AND totalNum > 1)"
			+ " LIMIT :pageNumber,:pageSize) AS V0")
	List<Object[]> findServiceReuseDegreeInfo(@Param("userId") String userId,
			@Param("interfaceName") String interfaceName,
			@Param("interfaceType") String interfaceType,
			@Param("startDate") String startDate,
			@Param("endDate") String endDate,
			@Param("reuseDegree") String reuseDegree,
			@Param("auditStatus") String auditStatus,
			@Param("pageNumber") long pageNumber,
			@Param("pageSize") long pageSize);

	/**
	 * 统计服务重用度总记录数
	 * 
	 * @param userId
	 *            ：用记标识
	 * 
	 * @param interfaceName
	 *            ：接口名称
	 * 
	 * @param interfaceType
	 *            ：接口类型
	 * 
	 * @param startDate
	 *            ：开始时间
	 * 
	 * @param endDate
	 *            ：结束时间
	 * 
	 * @param reuseDegree
	 *            ：重用度
	 * 
	 * @param auditStatus
	 *            ：审核状态
	 * 
	 * @return
	 */
	@Query(nativeQuery = true, value = "SELECT COUNT(V0.IF_HTTP_ID) AS totalNum "
			+ " FROM  (SELECT * FROM "
			+ " (SELECT h0.IF_HTTP_ID, "
			+ " (SELECT COUNT(s11.FIELD_SYSTEM_ID) FROM t_cook_bu_field_system s11, t_esb_bu_if_token t01  WHERE s11.FIELD_SYSTEM_ID=t01.IF_CALL_SYSTEM_ID and t01.IF_ID=h0.IF_HTTP_ID) AS totalNum "
			+ " FROM t_esb_bu_if_http h0 "
			+ " WHERE 1=1 "
			+ " AND (''=:auditStatus OR h0.AUDIT_STATUS =:auditStatus) "
			+ " AND (''=:interfaceName OR h0.IF_NAME LIKE :interfaceName) "
			+ " AND (''=:interfaceType OR h0.IF_TYPE =:interfaceType) "
			+ " AND (''=:startDate OR h0.CREATED_DATE >= :startDate) "
			+ " AND (''=:endDate OR h0.CREATED_DATE <= :endDate)  "
			+ " AND (''=:userId OR EXISTS(SELECT * FROM t_cook_bu_field_system S0,t_cook_bu_user_system U0 WHERE S0.FIELD_SYSTEM_ID=h0.FIELD_SYSTEM_ID AND S0.FIELD_SYSTEM_ID=U0.FIELD_SYSTEM_ID AND U0.USER_ID=:userId)) "
			+ " ) AS V1"
			+ " WHERE ''=:reuseDegree OR ('0'=:reuseDegree AND totalNum=0) OR ('1' =:reuseDegree AND totalNum  = 1) OR ('2'=:reuseDegree AND totalNum > 1)"
			+ " ) AS V0")
	List<Object> findServiceReuseDegreeTotalNum(@Param("userId") String userId,
			@Param("interfaceName") String interfaceName,
			@Param("interfaceType") String interfaceType,
			@Param("startDate") String startDate,
			@Param("endDate") String endDate,
			@Param("reuseDegree") String reuseDegree,
			@Param("auditStatus") String auditStatus);

	/**
	 * 查询重用服务系统信息
	 * 
	 * @param interfaceId
	 *            ：接口标识
	 * @param pageNumber
	 *            ：页码
	 * @param pageSize
	 *            ：页大小
	 * @return
	 */
	@Query(nativeQuery = true, value = "SELECT  "
			+ " (SELECT s.SYSTEM_NAME FROM t_cook_bu_field_system s WHERE s.FIELD_SYSTEM_ID=V0.IF_CALL_SYSTEM_ID) AS systemName, "
			+ " V0.APPLY_USER_CODE AS userAccount, "
			+ " (SELECT u.USER_NAME FROM t_cook_db_user u WHERE u.USER_CODE=V0.APPLY_USER_CODE) AS userName  "
			+ " FROM "
			+ " (SELECT t0.IF_CALL_SYSTEM_ID,t0.APPLY_USER_CODE FROM t_esb_bu_if_token t0 "
			+ " WHERE t0.IF_ID=:interfaceId "
			+ " LIMIT :pageNumber,:pageSize) AS V0")
	List<Object[]> findReuseServiceSystemInfo(
			@Param("interfaceId") String interfaceId,
			@Param("pageNumber") long pageNumber,
			@Param("pageSize") long pageSize);

	/**
	 * 查询重用服务系统总记录数
	 * 
	 * @param interfaceId
	 *            ：接口标识
	 * @return
	 */
	@Query(nativeQuery = true, value = "SELECT COUNT(t0.IF_CALL_SYSTEM_ID) AS total FROM t_esb_bu_if_token t0 "
			+ " WHERE t0.IF_ID=:interfaceId")
	List<Object> findServiceReuseSystemTotalNum(
			@Param("interfaceId") String interfaceId);

	@Query(nativeQuery = true, value = "SELECT (SELECT COUNT(H0.IF_HTTP_ID) AS totalAmount FROM t_esb_bu_if_http h0) AS totalAmount, "
			+ " COUNT(V0.reuseAmount) AS reuseAmount "
			+ " FROM "
			+ " (SELECT  COUNT(t0.IF_CALL_SYSTEM_ID) AS reuseAmount FROM t_esb_bu_if_token t0 "
			+ " WHERE EXISTS(SELECT * FROM  "
			+ " t_cook_bu_field_system S0, "
			+ " t_cook_bu_user_system U0  "
			+ " WHERE S0.FIELD_SYSTEM_ID=t0.IF_PUB_SYSTEM_ID  "
			+ " AND S0.FIELD_SYSTEM_ID=U0.FIELD_SYSTEM_ID  "
			+ " AND U0.USER_ID=:userId) "
			+ " GROUP BY t0.IF_ID "
			+ " HAVING reuseAmount>1) AS V0;")
	List<Object[]> getSummaryInfo(@Param("userId") String userId);
}
